On my first day in my job in a small consulting firm, I was staffed on a short project for three days.
The work was simple.
There were many folders on the network drive and each folder had hundreds of files in it.
I had to follow these three steps:
Select the file and copy its name.Paste that name in a cell in Excel and hit Enter.Move to the next file and repeat step 1 & 2.Sounds simple right?
It was – Simple and a huge waste of time.
What took me three days could have been done in a few minutes if I knew the right techniques.
In this tutorial, I will show you different ways to make this entire process super fast and super easy (with and without VBA).
This Tutorial Covers:
ToggleLimitations of the methods shown in this tutorial: With the techniques shown below, you will only be able to get the names of the files within the main folder. You will not get the names of the files in the sub-folders within the main folder. Here is a way to get names of files from folders and sub-folders using Power QueryUsing FILES Function to Get a List of File Names from a FolderHeard of FILES function before?
Don’t worry if you haven’t.
It is from the childhood days of Excel spreadsheets (a version 4 formula).
While this formula does not work in the worksheet cells, it still works in named ranges. We will use this fact to get the list of file names from a specified folder.
Now, suppose you have a folder with the name – ‘Test Folder‘ on the desktop, and you want to get a list of file names for all the files in this folder.
Here are the steps that will give you the file names from this folder:
In cell A1, enter the folder complete address followed by an asterisk sign (*)For example, if your folder in the C drive, then the address would look likeC:\Users\Sumit\Desktop\Test Folder\*
If you are not sure how to get the folder address, use the following method:In the folder from which you want to get the file names, either create a new Excel Workbook or open an existing workbook in the folder and use the below formula in any cell. This formula will give you the folder address and adds an asterisks sign (*) at the end. Now you can copy-paste (paste as value) this address in any cell (A1 in this example) in the workbook in which you want the file names.=REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")[If you have created a new workbook in the folder to use the above formula and get the folder address, you may want to delete it so that it doesn’t feature in the list of files in that folder]Go to the ‘Formulas’ tab and click on the ‘Define Name’ option.
In the New Name dialogue box, use the following detailsName: FileNameList (feel free to choose whatever name you like)Scope: WorkbookRefers to: =FILES(Sheet1!$A$1)
Now to get the list of files, we will use the named range within an INDEX function. Go to cell A3 (or any cell where you want the list of names to start) and enter the following formula:=IFERROR(INDEX(FileNameList,ROW()-2),"")Drag this down and it will give you a list of all the file names in the folder
Want to Extract Files with a Specific Extension??
If you want to get all the files with a particular extension, just change the asterisk with that file extension. For example, if you want only excel files, you can use *xls* instead of *
So the folder address that you need to use would be C:\Users\Sumit\Desktop\Test Folder\*xls*
Similarly, for word document files, use *doc*
How does this work?
FILES formula retrieves the names of all the files of the specified extension in the specified folder.
In the INDEX formula, we have given the file names as the array and we return the 1st, 2nd, 3rd file names and so on using the ROW function.
Note that I have used ROW()-2, as we started from the third row onwards. So ROW()-2 would be 1 for the first instance, 2 for the second instance when the row number is 4, and so on and so forth.
Watch Video – Get List of File Names from a Folder in Excel
Using VBA Get a List of All the File Names from a FolderNow, I must say that the above method is a bit complex (with a number of steps).
It’s, however, a lot better than doing this manually.
But if you’re comfortable with using VBA (or if you’re good at following exact steps that I am going to list below), you can create a custom function (UDF) that can easily get you the names of all the files.
The benefit of using a User Defined Function (UDF) is that you can save the function in a personal macro workbook and reuse it easily without repeating the steps again and again. You can also create an add-in and share this function with others.
Now let me first give you the VBA code that will create a function to get the list of all the file names from a folder in Excel.
Function GetFileNames(ByVal FolderPath As String) As VariantDim Result As VariantDim i As IntegerDim MyFile As ObjectDim MyFSO As ObjectDim MyFolder As ObjectDim MyFiles As ObjectSet MyFSO = CreateObject("Scripting.FileSystemObject")Set MyFolder = MyFSO.GetFolder(FolderPath)Set MyFiles = MyFolder.FilesReDim Result(1 To MyFiles.Count)i = 1For Each MyFile In MyFilesResult(i) = MyFile.Namei = i + 1Next MyFileGetFileNames = ResultEnd FunctionThe above code will create a function GetFileNames that can be used in the worksheets (just like regular functions).
Where to put this code?
Follow the steps below to copy this code in the VB Editor.
Go to the Developer tab.
Click on the Visual Basic button. This will open the VB Editor.
In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key).
Double click on the Module object and copy and paste the above code into the module code window.
How to Use this Function?
Below are the steps to use this function in a worksheet:
In any cell, enter the folder address of the folder from which you want to list the file names.In the cell where you want the list, enter the following formula (I am entering it in cell A3):=IFERROR(INDEX(GetFileNames($A$1),ROW()-2),"")Copy and paste the formula in the cells below to get a list of all the files.
Note that I entered the folder location in a cell and then used that cell in the GetFileNames formula. You can also hard code the folder address in the formula as shown below:
=IFERROR(INDEX(GetFileNames("C:\Users\Sumit\Desktop\Test Folder"),ROW()-2),"")In the above formula, we have used ROW()-2 and we started from the third row onwards. This made sure that as I copy the formula in the cells below, it will get incremented by 1. In case you’re entering the formula in the first row of a column, you can simply use ROW().
How does this formula work?
The GetFileNames formula returns an array that holds the names of all the files in the folder.
The INDEX function is used to list one file name per cell, starting from the first one.
IFERROR function is used to return blank instead of the #REF! error which is shown when a formula is copied in a cell but there are no more file names to list.
Using VBA Get a List of All the File Names with a Specific ExtensionThe above formula works great when you want to get a list of all the file names from a folder in Excel.
But what if you want to get the names of only the video files, or only the Excel files, or only the file names that contain a specific keyword.
In that case, you can use a slightly different function.
Below is the code that will allow you get all the file names with a specific keyword in it (or of a specific extension).
Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As VariantDim Result As VariantDim i As IntegerDim MyFile As ObjectDim MyFSO As ObjectDim MyFolder As ObjectDim MyFiles As ObjectSet MyFSO = CreateObject("Scripting.FileSystemObject")Set MyFolder = MyFSO.GetFolder(FolderPath)Set MyFiles = MyFolder.FilesReDim Result(1 To MyFiles.Count)i = 1For Each MyFile In MyFilesIf InStr(1, MyFile.Name, FileExt) 0 ThenResult(i) = MyFile.Namei = i + 1End IfNext MyFileReDim Preserve Result(1 To i - 1)GetFileNamesbyExt = ResultEnd FunctionThe above code will create a function ‘GetFileNamesbyExt‘ that can be used in the worksheets (just like regular functions).
This function takes two arguments – the folder location and the extension keyword. It returns an array of file names that match the given extension. If no extension or keyword is specified, it will return all the file names in the specified folder.
Syntax: =GetFileNamesbyExt(“Folder Location”,”Extension”)
Where to put this code?
Follow the steps below to copy this code in the VB Editor.
Go to the Developer tab.Click on the Visual Basic button. This will open the VB Editor.In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key).Double click on the Module object and copy and paste the above code into the module code window.How to Use this Function?
Below are the steps to use this function in a worksheet:
In any cell, enter the folder address of the folder from which you want to list the file names. I have entered this in cell A1.In a cell, enter the extension (or the keyword), for which you want all the file names. I have entered this in cell B1.In the cell where you want the list, enter the following formula (I am entering it in cell A3):=IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")Copy and paste the formula in the cells below to get a list of all the files.
How about you? Any Excel tricks that you use to make life easy. I would love to learn from you. Share it in the comment section!
You May Also Like the Following Excel Tutorials:
Filter cells with bold font format.How to Combine Multiple Excel Files into One Excel Workbook.Creating a Drop Down Filter to Extract Data Based on Selection.Using VBA FileSystemObject (FSO) in Excel.